﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Reflection;
using System.IO;
using System.Data.OleDb;
using System.Security.Cryptography;
using System.Data;
using ThuVien;
namespace TranTekBE.Upload
{
    public partial class Upload : System.Web.UI.Page
    {
        public string chartm = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            // vẽ biểu đồ tròn hàng tháng.
            string sql = "SELECT TOP 1 * FROM tbl_Upload ORDER BY ID DESC";
            DataSet chartmm = FunctionsUtils.Database2.ExecuteQuery(sql);
            if (chartmm != null)
            {
                foreach (DataRow row in chartmm.Tables[0].Rows)
                {
                    chartm += "['Bán nhanh'," + row[1].ToString() + "],['Bán vừa'," + row[2].ToString() + "],['Bán chậm'," + row[3].ToString() + "]";
                }
            }
        }
        float loai1 = 0;
        float loai2 = 0;
        float loai3 = 0;
        float tong1 = 0;
        float tong2 = 0;
        float tong3 = 0;
        protected void btnupload_Click(object sender, EventArgs e)
        {
            // chuỗi kiểm tra file upload
            lblthongbao.ForeColor = System.Drawing.Color.Red;
            string excelContentType = "application/vnd.ms-excel";
            string excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            if (!fileupload.HasFile)
            {
                lblthongbao.Text = "Vui lòng chọn file";
            }
            else
            {
                try
                {
                    if (fileupload.PostedFile.ContentType != excelContentType && fileupload.PostedFile.ContentType != excel2010ContentType)
                    {
                        lblthongbao.Text = "Định dạng file không đúng";
                    }
                    else
                    {
                        /* Lưu file xuống thử mục Temp*/
                        Random rand = new Random();
                        int random = rand.Next(123, 999);
                        string namefile = random.ToString() + fileupload.FileName;
                        string path = string.Concat(Server.MapPath("~/Upload/Temp/" + namefile));
                        fileupload.SaveAs(path);
                        lblthongbao.Text = "Upload thành công";
                        lblthongbao.ForeColor = System.Drawing.Color.Green;
                        string HASH = GetSHA1Hash(path);
                        string checkHash = "SELECT * FROM tbl_Upload WHERE Hash = '" + HASH + "'";
                        DataSet cHash = FunctionsUtils.Database2.ExecuteQuery(checkHash);
                        bool ch = false;
                        if (cHash.Tables[0].Rows.Count > 0)
                        {
                            lblthongbao.ForeColor = System.Drawing.Color.Blue;
                            lblthongbao.Text = "Chú ý: Hệ thống phát hiện ra file " + fileupload.FileName + " đã tồn tại.";
                            ch = true;
                        }
                        /* Đọc file excel trong thử mục Temp*/
                        string excelConnectionString = string.Empty;
                        //connection String for xls file format.
                        if (fileupload.PostedFile.ContentType == excelContentType)
                        {
                            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                            path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        }
                        //connection String for xlsx file format.
                        else if (fileupload.PostedFile.ContentType == excel2010ContentType)
                        {
                            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                            path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        }
                        //Create Connection to Excel work book and add oledb namespace
                        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                        excelConnection.Open();
                        DataTable dt = new DataTable();
                        dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        String[] excelSheets = new String[dt.Rows.Count];
                        int t = 0;
                        //excel data saves in temp file here.
                        foreach (DataRow row in dt.Rows)
                        {
                            excelSheets[t] = row["TABLE_NAME"].ToString();
                            t++;
                        }
                        // Truy vấn sheet1 của file excel
                        string query = string.Format("SELECT TOP 5 * FROM [{0}]", excelSheets[0]+"A1:G6");
                        DataTable dtt = new DataTable();
                        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection))
                        {
                            dataAdapter.Fill(dtt);
                        }
                        lblshow.Text = "5 dòng đầu tiên của file";
                        Gridshow.DataSource = dtt;
                        Gridshow.DataBind();

                        query = string.Format("SELECT * FROM [{0}]", excelSheets[0]);
                        DataTable datacheck = new DataTable();
                        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection))
                        {
                            dataAdapter.Fill(datacheck);
                        }
                        //Số Lượng Tồn (Theo DVT NN)

                        object sumObject;
                        sumObject = datacheck.Compute("Sum([Tổng Giá Trị Tồn Hiện tại])", "[Months To Turn Inventory] < 45");
                        tong1 = float.Parse(sumObject.ToString());
                        sumObject = datacheck.Compute("Sum([Tổng Giá Trị Tồn Hiện tại])", "[Months To Turn Inventory] >= 45");
                        tong2 = float.Parse(sumObject.ToString());
                        sumObject = datacheck.Compute("Sum([Tổng Giá Trị Tồn Hiện tại])", "[Months To Turn Inventory] > 60");
                        tong3 = float.Parse(sumObject.ToString());
                        tong2 = tong2 - tong3;
                        //------------------
                        sumObject = datacheck.Compute("Sum([Số Lượng Tồn (Theo DVT NN)])", "[Months To Turn Inventory] < 45");
                        loai1 = float.Parse(sumObject.ToString());
                        sumObject = datacheck.Compute("Sum([Số Lượng Tồn (Theo DVT NN)])", "[Months To Turn Inventory] >= 45");
                        loai2 = float.Parse(sumObject.ToString());
                        sumObject = datacheck.Compute("Sum([Số Lượng Tồn (Theo DVT NN)])", "[Months To Turn Inventory] > 60");
                        loai3 = float.Parse(sumObject.ToString());
                        loai2 = loai2 - loai3;
                        //-- lấy Cửa Hàng
                        DataRow CuaHang = datacheck.Rows[3];
                        string str_CuaHang = CuaHang[0].ToString();
                        //-- lấy Ngày tạo
                        DataRow NgayTao = datacheck.Rows[3];
                        string str_date = NgayTao[10].ToString().Split(' ')[0];
                        if (!ch)
                        {
                            string ins = "INSERT INTO tbl_Upload VALUES (" + loai1 + "," + loai2 + "," + loai3 + ",'" + (int)tong1 + "','" + (int)tong2 + "','" + (int)tong3 + "','" + HASH + "','" + str_date + "',N'" + str_CuaHang + "')";
                            FunctionsUtils.Database2.ExecuteNonQuery(ins);
                        }
                        

                    }
                }
                catch (Exception ex)
                {
                    lblthongbao.ForeColor = System.Drawing.Color.Red;
                    lblthongbao.Text = ex.Message;//"Có lổi xảy ra trong quá trình upload";
                }
            }
        }
        private static System.IO.FileStream GetFileStream(string pathName)
        {
            return (new System.IO.FileStream(pathName, System.IO.FileMode.Open,
                      System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite));
        }
        public static string GetSHA1Hash(string pathName)
        {
            string strResult = "";
            string strHashData = "";

            byte[] arrbytHashValue;
            System.IO.FileStream oFileStream = null;

            System.Security.Cryptography.SHA1CryptoServiceProvider oSHA1Hasher =
                       new System.Security.Cryptography.SHA1CryptoServiceProvider();

            try
            {
                oFileStream = GetFileStream(pathName);
                arrbytHashValue = oSHA1Hasher.ComputeHash(oFileStream);
                oFileStream.Close();

                strHashData = System.BitConverter.ToString(arrbytHashValue);
                strHashData = strHashData.Replace("-", "");
                strResult = strHashData;
            }
            catch (System.Exception ex)
            {
                
            }

            return (strResult);
        }
    }
}